/*stuinfo.c*/
#include "mysql.h"
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

struct student    //对应表的列属性
{
    int id;       //学号
    char name[20];    //姓名
    char sex[8];  //性别
    char mailbox[40]; //邮箱地址
    char studep[100]; //所在院系
};

/* 初始化数据库 */
MYSQL* initial(MYSQL *con) {
    if (mysql_library_init(0, NULL, NULL)) {
        fprintf(stderr, "Could not initialize MySQL library.\n");
        return NULL;
    }
    con = mysql_init(NULL);
    if (con == NULL) {
        fprintf(stderr, "mysql_init() failed: %s\n", mysql_error(con));
    }
    return con;
}

/* 连接数据库 */
MYSQL* myconnect(MYSQL *con, char *username, char *passwd, char *hostip, char *db) {
    if (mysql_real_connect(con, hostip, username, passwd, db, 0, NULL, 0) == NULL) {
        fprintf(stderr, "mysql_real_connect() failed: %s\n", mysql_error(con));
        mysql_close(con);
        return NULL;
    }
    printf("Connected to MySQL server!\n");
    return con;
}

void createDB(MYSQL *con, char *cmd) {
    if (mysql_query(con, cmd)) {
        fprintf(stderr, "CreateDB failed: %s\n", mysql_error(con));
    } else {
        printf("Database created successfully.\n");
        // 创建后自动选择数据库
        if (mysql_select_db(con, "student")) {
            fprintf(stderr, "mysql_select_db() failed: %s\n", mysql_error(con));
        }
    }
}

void createTable(MYSQL *con,char *db,char *cmd){
    if( mysql_select_db(con,db) ){
        printf("select DB failed\n");
    }
    if( mysql_query(con,cmd) ){
        printf("CreateTable failed\n");
    }
    else
        printf("Create table successful\n");
}

void displayTable(MYSQL *con,char *query){
    MYSQL_RES *mysql_res;
    MYSQL_ROW mysql_row;
    int i,j;
    unsigned long *lengths;
    int num_row,num_col;

    if( mysql_real_query(con, query, (unsigned int)strlen(query)) != 0 ){
        printf("Query failed\n");
        return;
    }

    //保存查询结果
    if( (mysql_res = mysql_store_result(con) ) == NULL ){
        printf("Store result failed\n");
        return;
    }

    // 获取查询结果的行数、列数
    num_row = mysql_num_rows(mysql_res);
    num_col = mysql_num_fields(mysql_res);
    printf("rows: %d, columns: %d\n", num_row, num_col);
    printf("****************************************\n");
    // 逐行显示结果
    for(i=0; i<num_row; i++){
        // 从保存的查询结果中获取下一行内容
        if( (mysql_row = mysql_fetch_row(mysql_res)) == NULL ){
            break;
        }
        // 获取列长度的数组
        lengths = mysql_fetch_lengths(mysql_res);
        for(j=0; j<num_col; j++){
            printf("column %d, length:%lu\n", j, lengths[j]);
            // 打印一行中各列的值
            printf("\t%s", mysql_row[j]?mysql_row[j]:"NULL");
        }
        printf("\n");
    }
    printf("****************************************\n");
    // 释放保存的查询结果
    mysql_free_result(mysql_res);
}

void InsertData(MYSQL *con){
    struct student student1;
    char query[1024];
    printf("please input student id : \n");
    scanf("%d", &student1.id);
    getchar();
    printf("please input student name:\n ");
    scanf("%s", student1.name);
    getchar();
    printf("please input student sex(male/female):\n ");
    scanf("%s", student1.sex);
    getchar();
    printf("please input student phone :\n ");
    scanf("%s", student1.mailbox);
    getchar();
    printf("please input student department :\n ");
    scanf("%s", student1.studep);
    getchar();
    sprintf(query, "insert into stuinfo(id, stuname,gender,mailbox,studep) values(%d,'%s','%s','%s','%s')", student1.id, student1.name, student1.sex, student1.mailbox, student1.studep);
    if( mysql_real_query(con, query, (unsigned int)strlen(query)) != 0 ){
        mysql_rollback(con);
        printf("insert failed\n");
    }
    mysql_commit(con);
    displayTable(con,"select * from stuinfo");
}

void deleteData(MYSQL *con){
    int id;
    char query[1024];
    printf("please input student id to be deleted\n");
    scanf("%d",&id);
    getchar();
    sprintf(query,"delete from stuinfo where id=%d",id);
    if( mysql_real_query(con, query, (unsigned int)strlen(query)) != 0 ){
        mysql_rollback(con);
        printf("delete failed\n");
    }
    mysql_commit(con);
    if( mysql_affected_rows(con)>0 )
        printf("delete successful\n");
    displayTable(con,"select * from stuinfo");
}

void updateData(MYSQL *con) {
    int id;
    char name[20], sex[8], mailbox[40], studep[100];
    char query[1024];

    printf("请输入要更新的学生的 ID: ");
    scanf("%d", &id);
    getchar();

    printf("请输入新的学生姓名: ");
    scanf("%s", name);
    getchar();

    printf("请输入新的学生性别 (male/female): ");
    scanf("%s", sex);
    getchar();

    printf("请输入新的学生邮箱: ");
    scanf("%s", mailbox);
    getchar();

    printf("请输入新的学生所在院系: ");
    scanf("%s", studep);
    getchar();

    sprintf(query, "UPDATE stuinfo SET stuname = '%s', gender = '%s', mailbox = '%s', studep = '%s' WHERE id = %d",
            name, sex, mailbox, studep, id);

    if (mysql_real_query(con, query, (unsigned int)strlen(query)) != 0) {
        mysql_rollback(con);
        printf("更新失败\n");
    } else {
        mysql_commit(con);
        if (mysql_affected_rows(con) > 0) {
            printf("更新成功\n");
        } else {
            printf("未找到匹配的记录\n");
        }
    }
    displayTable(con, "SELECT * FROM stuinfo");
}


int menu(){
    int choose;
    printf("********************************\n");
    printf("*****student information system****\n");
    printf("********************************\n");
    printf("1. create database\n");
    printf("2. create table\n");
    printf("3. insert data\n");
    printf("4. delete data\n");
    printf("5. update data\n");
    printf("0. exit\n");
    printf("please input choose\n");
    scanf("%d",&choose);
    getchar();
    return choose;
}

void handleMenu(MYSQL *mysql){
    int choose;
    while (1) {
        choose=menu();
        switch(choose){
            case 1: createDB(mysql,"create database student");break;
            case 2: createTable(mysql,"student","create table stuinfo(id INT, stuname TEXT,gender TEXT,mailbox TEXT,studep TEXT)");break;
            case 3: InsertData(mysql);break;
            case 4: deleteData(mysql);break;
            case 5: updateData(mysql); break;
            case 0: printf("exit\n"); return;
            default: printf("无效的选择，请重新输入。\n");
        }
    }
}

int main(void){
    MYSQL *mysql_handle=NULL;
    mysql_handle=initial(mysql_handle);
    if ( NULL==mysql_handle ){
        fprintf(stderr, "%s\n","Initialize failed");
        exit(1);
    }
    mysql_handle=myconnect(mysql_handle,"root","root","localhost","");
    handleMenu(mysql_handle);
    mysql_close(mysql_handle);
    mysql_library_end();
    return 0;
}    
